/*---------------------------------------------------------------
 * Confidential and Proprietary
 * Copyright 2006 CMO & Hewlett-Packard Development Company, L.P.
 * All Rights Reserved
 *---------------------------------------------------------------
 * Project Name    : MPS
 * Sub Project Name: MPS
 *
 * Class Name      : ExcelReader
 * Purpose         : 
 *---------------------------------------------------------------
 * Modification Log
 * Date         Ver. #      Programmer       Description
 *
 * Mar 14, 2006    1.0          zhangzhm          Initial
 *---------------------------------------------------------------
 */
package com.erdos.common.util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelReader {
	
	private static final int MAX_COLUMN_NUM = 255;
	
    private final static Log logger = LogFactory.getLog(ExcelReader.class);
    /**
     * 
     * @param fileName
     * @return
     */
    public static String[] getSheetNames(String fileName)
	{
		String[] sheetNames = null;

		try
		{		
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileName));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			
			int sheetCount = wb.getNumberOfSheets();
			sheetNames = new String[sheetCount];
			for(int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex ++)
			{
				sheetNames[sheetIndex] = wb.getSheetName(sheetIndex);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return sheetNames;
	}
    
    /**
     * 
     * @param inputStream
     * @return
     */
    public static String[] getSheetNames(InputStream inputStream)
    {
        String[] sheetNames = null;

        try
        {       
            POIFSFileSystem fs = new POIFSFileSystem(inputStream);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            
            int sheetCount = wb.getNumberOfSheets();
            sheetNames = new String[sheetCount];
            for(int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex ++)
            {
                sheetNames[sheetIndex] = wb.getSheetName(sheetIndex);
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        
        return sheetNames;
    }
    
    /**
     * 
     * @param fileName
     * @param sheetName
     * @return
     */
	public static TableModel read(String fileName, String sheetName)
	{
		DefaultTableModel tableModel = null;
		try
		{		
			int rowCount = 0;
			int colCount = 0;

			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileName));

			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheet(sheetName);

			rowCount = sheet.getLastRowNum() + 1; 

			for(int rowIndex = 0; rowIndex < rowCount; rowIndex ++)
			{
				HSSFRow row = sheet.getRow(rowIndex);
				if(row != null && row.getLastCellNum() > colCount)
				{
					colCount = row.getPhysicalNumberOfCells();
                    logger.debug("colCount: "+ colCount);
				}
			}

            logger.debug("final colCount: "+ colCount);
			tableModel = new DefaultTableModel(rowCount, colCount);

			List formats = HSSFDataFormat.getBuiltinFormats();

			for(int rowIndex = 0; rowIndex < rowCount; rowIndex ++)
			{
				HSSFRow row = sheet.getRow(rowIndex);
				if(row != null)
				{
					for(int colIndex = 0; colIndex < colCount; colIndex ++)
					{
						HSSFCell cell = row.getCell((short) colIndex);
						
						if(cell != null)
						{
							Object value = null;
							
							switch(cell.getCellType())
							{
								case HSSFCell.CELL_TYPE_BOOLEAN :
									value = new Boolean(cell.getBooleanCellValue());
									break;

								case HSSFCell.CELL_TYPE_NUMERIC :
									value = new Double(cell.getNumericCellValue());
									short format = cell.getCellStyle().getDataFormat();
									if ((0xe <= format && format <= 0x16) 
										|| (0x17 <= format && format <= 0x24)
										|| (0x2d <= format && format <= 0x2f))
									{
										value = cell.getDateCellValue();
									}
									break;

								case HSSFCell.CELL_TYPE_FORMULA :
									value = new Double(cell.getNumericCellValue());
									if(Double.isNaN(((Double) value).doubleValue()))
									{
										value = cell.getStringCellValue();
									}
									break;

								default : 
									value = cell.getStringCellValue();
							}
						
//							System.out.println("[ExcelReader.readAsTableModel] [" + rowIndex + ", " + colIndex + "]=" + value);
							tableModel.setValueAt(value, rowIndex, colIndex);
						}
					}
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return tableModel;
	}
    
    /**
     * 
     * @param inputStream
     * @param sheetName
     * @return
     */
    public static TableModel read(InputStream inputStream, String sheetName)
    {
        DefaultTableModel tableModel = null;
        try
        {       
            int rowCount = 0;
            int colCount = 0;

            POIFSFileSystem fs = new POIFSFileSystem(inputStream);

            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheet(sheetName);

            rowCount = sheet.getLastRowNum() + 1; 

            for(int rowIndex = 0; rowIndex < rowCount; rowIndex ++)
            {
                HSSFRow row = sheet.getRow(rowIndex);
                if(row != null && row.getLastCellNum() > colCount)
                {
                    colCount = row.getPhysicalNumberOfCells();
                    logger.debug("colCount: "+ colCount);
                }
            }
            
            logger.debug("final colCount: "+ colCount);
            tableModel = new DefaultTableModel(rowCount, colCount);

            List formats = HSSFDataFormat.getBuiltinFormats();

            for(int rowIndex = 0; rowIndex < rowCount; rowIndex ++)
            {
                HSSFRow row = sheet.getRow(rowIndex);
                if(row != null)
                {
                    for(int colIndex = 0; colIndex < colCount; colIndex ++)
                    {
                        HSSFCell cell = row.getCell((short) colIndex);
                        
                        if(cell != null)
                        {
                            Object value = null;
                            
                            switch(cell.getCellType())
                            {
                                case HSSFCell.CELL_TYPE_BOOLEAN :
                                    value = new Boolean(cell.getBooleanCellValue());
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC :
                                    value = new Double(cell.getNumericCellValue());
                                    short format = cell.getCellStyle().getDataFormat();
                                    if ((0xe <= format && format <= 0x16) 
                                        || (0x17 <= format && format <= 0x24)
                                        || (0x2d <= format && format <= 0x2f))
                                    {
                                        value = cell.getDateCellValue();
                                    }
                                    break;

                                case HSSFCell.CELL_TYPE_FORMULA :
                                    value = new Double(cell.getNumericCellValue());
                                    if(Double.isNaN(((Double) value).doubleValue()))
                                    {
                                        value = cell.getStringCellValue();
                                    }
                                    break;

                                default : 
                                    value = cell.getStringCellValue();
                            }
                        
                            //System.out.println("[ExcelReader.readAsTableModel] [" + rowIndex + ", " + colIndex + "]=" + value);
                            tableModel.setValueAt(value, rowIndex, colIndex);
                        }
                    }
                }
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return tableModel;
    }
	@SuppressWarnings("unchecked")
    public static Object getCellValue(HSSFCell cell) {
		if (cell == null) {
			return null;
		}

		Object value = null;

		switch (cell.getCellType()) {

		case HSSFCell.CELL_TYPE_BOOLEAN:
			value = new Boolean(cell.getBooleanCellValue());
			break;

		case HSSFCell.CELL_TYPE_NUMERIC:
			value = new Double(cell.getNumericCellValue());
			short format = cell.getCellStyle().getDataFormat();
			if ((0xe <= format && format <= 0x16) || (0x17 <= format && format <= 0x24)
					|| (0x2d <= format && format <= 0x2f)) {
				value = cell.getDateCellValue();
			}
			break;

		case HSSFCell.CELL_TYPE_FORMULA:
			value = new Double(cell.getNumericCellValue());
			if (Double.isNaN(((Double) value).doubleValue())) {
				value = cell.getStringCellValue();
			}
			break;

		default:
			value = cell.getStringCellValue();
		}
		return value;
	}
	@SuppressWarnings("unchecked")
    public static List getRowValues(HSSFRow row, int startIndex, int endIndex) {
    	if (startIndex > MAX_COLUMN_NUM) {
    		throw new IllegalArgumentException("Start cell index can not be larger than "
					+ MAX_COLUMN_NUM);
    	}
    	if (startIndex < 0) {
    		startIndex = 0;
    	}
		if (endIndex < 0 || endIndex > MAX_COLUMN_NUM) {
			endIndex = MAX_COLUMN_NUM;
		}
		List result = new ArrayList(endIndex - startIndex);
		for (int i = startIndex; i < endIndex; i++) {
			HSSFCell cell = row.getCell((short) i);
			if (cell == null) {
				break;
			}
			Object value = getCellValue(cell);
			result.add(value);
		}
		return result;
	}
	@SuppressWarnings("unchecked")
    public static List getRowValues(HSSFRow row) {
    	return getRowValues(row, 0, -1);
    }
    
}

